package com.ayg.utils;

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFDataValidationHelper;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author owen.he
 */
public class ExcelUtils {
    private static Log logger = LogFactory.getLog(ExcelUtils.class);

    private static int DEFAULT_PAGE_ROWS = 20000;

    public static final String EXCEL_2003 = "2003";
    public static final String EXCEL_2007 = "2007";

    public static void export(HttpServletResponse response, String strOfTitle, List<Map<String, Object>> listOfRows){
        if(StringUtils.isBlank(strOfTitle)){
            return;
        }
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
      	String fileName = DateFormatUtils.format(Calendar.getInstance().getTime(), "yyyyMMddHHmmss") + ".xlsx";
        response.setHeader("Content-disposition", "attachment; filename=" + fileName);
        SXSSFWorkbook wb = new SXSSFWorkbook(DEFAULT_PAGE_ROWS + 2);
        Sheet sheet = wb.createSheet("sheet1");
        int rowNum = 0;
        //创建表头
        Row row = sheet.createRow(rowNum++);
        int i = 0;
        List<String> listOfTitle = new ArrayList<String>();
        String[] arrOfTitle = strOfTitle.split(",");
        String[] arrOfKV = null;
        for (String kv : arrOfTitle) {
            arrOfKV = kv.split(":");

            row.createCell(i).setCellValue(arrOfKV[1]);
            listOfTitle.add(arrOfKV[0]);
            i++;
        }

        Map<String, Object> map = null;
        String title = null;
        Object v = null;
        for(i = 0; i < listOfRows.size(); i++){
            map = listOfRows.get(i);
            if(MapUtils.isEmpty(map)){
                continue;
            }
            Row r = sheet.createRow(rowNum++);
            for(int j = 0; j < listOfTitle.size(); j++){
                title = listOfTitle.get(j);
                v = map.get(title);
                if (v == null) {
                    r.createCell(j).setCellValue("");
                } else if (v instanceof Boolean){
                        r.createCell(j).setCellValue(Boolean.TRUE.equals(v) ? "是" : "否");
                } else if (v instanceof Integer || v instanceof Long || v instanceof Float || v instanceof Double){
                    r.createCell(j).setCellValue(Double.valueOf(v.toString()));
                } else if (v instanceof String){
                    r.createCell(j).setCellValue((String) v);
                }
            }
        }

		//http输出
		OutputStream out = null;
		try {
			out = response.getOutputStream();
			wb.write(out);
			out.flush();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (Exception e2) {
					e2.printStackTrace();
				}
			}
			if (wb != null) {
				//清除excel缓存文件
				try {
					wb.dispose();
					wb.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
    }

    /**
     * 设置单元格的下拉属性限制
     * @param sheet
     * @param validatedList
     * @param firstRow          起始行
     * @param lastRow           结束行
     * @param firstColumn       起始列
     * @param lastColumn        结束列
     * @return
     */
    public static XSSFSheet setDataValidationOfCell2007(XSSFSheet sheet, String[] validatedList, int firstRow, int lastRow,
            int firstColumn, int lastColumn){
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        // 加载下拉列表内容
        DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(validatedList);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList periodOfProbation = new CellRangeAddressList(firstRow, lastRow, firstColumn, lastColumn);
        // 数据有效性对象
        XSSFDataValidation validation =(XSSFDataValidation) dvHelper.createValidation(dvConstraint, periodOfProbation);
        validation.setShowErrorBox(true);
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        sheet.addValidationData(validation);
        return sheet;
    }

    /**
     * 设置单元格的下拉属性限制
     * @param sheet
     * @param validatedList
     * @param firstRow          起始行
     * @param lastRow           结束行
     * @param firstColumn       起始列
     * @param lastColumn        结束列
     * @return
     */
    public static HSSFSheet setDataValidationOfCell2003(HSSFSheet sheet, String[] validatedList, int firstRow, int lastRow,
            int firstColumn, int lastColumn){
        HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
        // 加载下拉列表内容
        DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(validatedList);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList periodOfProbation = new CellRangeAddressList(firstRow, lastRow, firstColumn, lastColumn);
        // 数据有效性对象
        HSSFDataValidation validation =(HSSFDataValidation) dvHelper.createValidation(dvConstraint, periodOfProbation);
        validation.setShowErrorBox(true);
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        sheet.addValidationData(validation);
        return sheet;
    }

    /**
     * 创建97-2003excel的工作薄，记得调用此方法后要flush和close输出流，调用本类的closeResponse方法就可以鸟
     * @param response
     * @param strOfTitle
     * @param sheetTitle
     * @return
     */
    public static HSSFWorkbook createExcel2003(HttpServletResponse response, String strOfTitle, String data ,String sheetTitle, String announce){
    	response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
      	String fileName = DateFormatUtils.format(Calendar.getInstance().getTime(), "yyyyMMddHHmmss") + ".xls";
        response.setHeader("Content-disposition", "attachment; filename=" + fileName);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet sheet = hssfWorkbook.createSheet(sheetTitle);
        HSSFDataFormat format = hssfWorkbook.createDataFormat();
        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
        cellStyle.setDataFormat(format.getFormat("@"));

        int rowNum = 0;
        //创建表头
        Row row = sheet.createRow(rowNum++);
        int i = 0;
        List<String> listOfTitle = new ArrayList<String>();
        HSSFDataFormat hssfDataFormat = hssfWorkbook.createDataFormat();

        Font fontOfRequired = hssfWorkbook.createFont();
        fontOfRequired.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fontOfRequired.setColor(HSSFColor.RED.index);

        HSSFCellStyle hssfCellStyleOfRequired = hssfWorkbook.createCellStyle();
        hssfCellStyleOfRequired.setDataFormat(hssfDataFormat.getFormat("@"));
        hssfCellStyleOfRequired.setFont(fontOfRequired);

        Font fontOfOptional = hssfWorkbook.createFont();
        fontOfOptional.setBoldweight(Font.BOLDWEIGHT_BOLD);

        HSSFCellStyle hssfCellStyle = hssfWorkbook.createCellStyle();
        hssfCellStyle.setDataFormat(hssfDataFormat.getFormat("@"));
        hssfCellStyle.setFont(fontOfOptional);

        String[] arrOfTitle = strOfTitle.split(",");
        String[] arrOfKV = null;
        for (String kv : arrOfTitle) {
            arrOfKV = kv.split(":");
            Cell cell = row.createCell(i);
            if(arrOfKV.length > 2 && "required".equals(arrOfKV[2])){
                cell.setCellStyle(hssfCellStyleOfRequired);
            }else {
                cell.setCellStyle(hssfCellStyle);
            }
            cell.setCellValue(arrOfKV[1]);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            listOfTitle.add(arrOfKV[0]);
            sheet.setColumnWidth(i,4500);
            
            //设置身份证列为文本 10000行
            if (arrOfKV[0].equals("idCard")) {
            	
            	for (int r=rowNum; r<10000; r++) {
            		Row idCardRow = sheet.createRow(r);
            		Cell idCardCell = idCardRow.createCell(i);
            		idCardCell.setCellStyle(hssfCellStyle);
                    idCardCell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    sheet.setColumnWidth(i,4500);
            	}
            }
            
            i++;
        }
        //设置一行示例数据
        String[] simulateData = data.split(":");
        int index = 0;
        row = sheet.createRow(rowNum++);
        for (String d : simulateData){
            Cell cell = row.createCell(index);
            cell.setCellValue(d);
            index++;
        }
        HSSFCellStyle hssfCellStyleOfTitle = hssfWorkbook.createCellStyle();
        hssfCellStyle.setDataFormat(hssfDataFormat.getFormat("@"));
        sheet.setDefaultColumnStyle(1, hssfCellStyleOfTitle);

        HSSFSheet sheetOfNote = hssfWorkbook.createSheet("填表说明(重要)");
        sheetOfNote.setColumnWidth(0,30000);
        hssfWorkbook.setActiveSheet(1);
        String[] announces = announce.split(";");
        for (int j=0;j<announces.length;j++){
            row = sheetOfNote.createRow(j);
            row.createCell(0).setCellValue(announces[j]);
            row.getCell(0).setCellStyle(hssfCellStyleOfRequired);
        }
        return hssfWorkbook;
    }

    /**
     * 创建2007excel的工作薄，记得调用此方法后要flush和close输出流，调用本类的closeResponse方法就可以鸟
     * @param response
     * @param strOfTitle
     * @param sheetTitle
     * @return
     */
    public static XSSFWorkbook createExcel2007(HttpServletResponse response, String strOfTitle, String sheetTitle){
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
      	String fileName = DateFormatUtils.format(Calendar.getInstance().getTime(), "yyyyMMddHHmmss") + ".xlsx";
        response.setHeader("Content-disposition", "attachment; filename=" + fileName);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        XSSFSheet sheet = xssfWorkbook.createSheet(sheetTitle);
        int rowNum = 0;
        //创建表头
        Row row = sheet.createRow(rowNum++);
        int i = 0;
        List<String> listOfTitle = new ArrayList<String>();
        XSSFDataFormat xssfDataFormat = xssfWorkbook.createDataFormat();

        Font fontOfRequired = xssfWorkbook.createFont();
        fontOfRequired.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fontOfRequired.setColor(HSSFColor.RED.index);

        XSSFCellStyle xssfCellStyleOfRequired = xssfWorkbook.createCellStyle();
        xssfCellStyleOfRequired.setDataFormat(xssfDataFormat.getFormat("@"));
        xssfCellStyleOfRequired.setFont(fontOfRequired);

        Font fontOfOptional = xssfWorkbook.createFont();
        fontOfOptional.setBoldweight(Font.BOLDWEIGHT_BOLD);

        XSSFCellStyle xssfCellStyle = xssfWorkbook.createCellStyle();
        xssfCellStyle.setDataFormat(xssfDataFormat.getFormat("@"));
        xssfCellStyle.setFont(fontOfOptional);

        String[] arrOfTitle = strOfTitle.split(",");
        String[] arrOfKV = null;
        for (String kv : arrOfTitle) {
            arrOfKV = kv.split(":");
            Cell cell = row.createCell(i);
            if(arrOfKV.length > 2 && "required".equals(arrOfKV[2])){
                cell.setCellStyle(xssfCellStyleOfRequired);
            }else {
                cell.setCellStyle(xssfCellStyle);
            }
            cell.setCellValue(arrOfKV[1]);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            listOfTitle.add(arrOfKV[0]);
            i++;
        }
        return xssfWorkbook;
    }

    /**
     * flush和close输出流
     * @param response
     * @param workbook
     */
    public static void closeResponse(HttpServletResponse response, Workbook workbook){
		//http输出
		OutputStream out = null;
		try {
            out = response.getOutputStream();
            workbook.write(out);
			out.flush();
		} catch (Exception e) {
            logger.error(e.getMessage());
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (Exception e2) {
                    logger.error(e2.getMessage());
				}
			}
			if (workbook != null) {
				//清除excel缓存文件
                //TODO
			}
		}
    }

    /**
     * 将excel单元格中的date解析粗来
     * @param cell
     * @return
     */
    public static Date parseDateFromExcel(Cell cell){
        if(cell == null){
            return null;
        }
        Date date = null;
        if(HSSFCell.CELL_TYPE_STRING == cell.getCellType() || XSSFCell.CELL_TYPE_STRING == cell.getCellType()){
            String strOfDate = cell.getStringCellValue();
            if(StringUtils.isBlank(strOfDate)){
                return null;
            }
            strOfDate = strOfDate.trim();
            try{
                date = DateUtils.parseDate(strOfDate,new String[]{"yyyy-MM-dd"});
                if(date == null){
                    date = DateUtils.parseDate(strOfDate, new String[]{"yyyy/MM/dd"});
                }
                if(date == null){
                    date = DateUtils.parseDate(strOfDate, new String[]{"yyyyMM"});
                }
                if(date == null){
                    date = DateUtils.parseDate(strOfDate, new String[]{"yyyy-MM"});
                }
                if(date == null){
                    date = DateUtils.parseDate(strOfDate, new String[]{"yyyy/MM"});
                }
                if(date == null){
                    date = DateUtils.parseDate(strOfDate, new String[]{"yyyy-MM-dd"});
                }
            } catch(Exception e){
            	
            }
        }
        if(HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType() || XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()){
            if(HSSFDateUtil.isCellDateFormatted(cell)){
                date = cell.getDateCellValue();
            }
        }
        return date;
    }

    /**
     * 将excel单元格中的String解析粗来
     * @param cell
     * @return
     */
    public static String parseStringFromExcel(Cell cell, Map<String,Object> configMap){
        if(cell == null){
            return null;
        }
        if(XSSFCell.CELL_TYPE_STRING != cell.getCellType()){
            return null;
        }
        String value = cell.getStringCellValue();
        if(org.apache.commons.lang3.StringUtils.isBlank(value)){
            return null;
        }
        value = value.trim();
        if(configMap != null && configMap.containsValue(value)){
            for (Iterator<String> iterator = configMap.keySet().iterator(); iterator.hasNext(); ) {
                Object k =  iterator.next();
                Object v = configMap.get(k);
                if(value.equals(v)){
                    return (String) k;
                }
            }
        }
        return value;
    }

    /**
     * 将excel单元格中的手机号码解析粗来，考虑到有可能用户没有设置列的格式，所以就得自己折腾那么一哈
     * @param cell
     * @return
     */
    public static String parsePhoneFromExcel(Cell cell, Map<String,Object> configMap){
        if(cell == null){
            return null;
        }
        String value = null;
        if(XSSFCell.CELL_TYPE_STRING == cell.getCellType()){
            value = parseStringFromExcel(cell, configMap);
        }
        if(XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()){
            Double valueOfDouble = cell.getNumericCellValue();
            value = Long.valueOf(valueOfDouble.longValue()).toString();
        }
        return value;
    }

    /**
     * 获取Excel的别名，如AA，AB等等
     * @param num
     * @return
     */
    public static String getExcelColumn(int num) {
        String columFiled = "";
        int chuNum = 0;
        int yuNum = 0;
        if(num >= 1 && num <= 26){
            columFiled = doHandle(num);
        }else{
            chuNum = num / 26;
            yuNum = num % 26;

            columFiled +=  doHandle(chuNum);
            columFiled +=  doHandle(yuNum);
        }
        return columFiled;
    }

    public static String doHandle(final int num) {
        String[] charArr = {"A","B","C","D","E","F","G","H","I","J","K","L","M"
                ,"N","O","P","Q","R","S","T","U","V","W","X","Y","Z"};
        return charArr[num-1].toString();
    }

    /**  
     * 创建一个名称  
     * @param workbook  
     */ 
    public static void creatExcelNameList(Workbook workbook,String excelHideName, String nameCode,int order,int size,boolean cascadeFlag){   
        Name name;   
        name = workbook.createName();   
        name.setNameName(nameCode);   
        name.setRefersToFormula(excelHideName+"!"+creatExcelNameList(order,size,cascadeFlag));   
    }   
     
    /**  
     * 名称数据行列计算表达式  
     * @param workbook  
     */ 
    public static String creatExcelNameList(int order,int size,boolean cascadeFlag){   
        char start = 'A';   
        if(cascadeFlag){   
            start = 'B';   
            if(size<=25){   
                char end = (char)(start+size-1);   
                return "$"+start+"$"+order+":$"+end+"$"+order;   
            }else{   
                char endPrefix = 'A';   
                char endSuffix = 'A';   
                if((size-25)/26==0||size==51){//26-51之间，包括边界（仅两次字母表计算）   
                    if((size-25)%26==0){//边界值   
                        endSuffix = (char)('A'+25);   
                    }else{   
                        endSuffix = (char)('A'+(size-25)%26-1);   
                    }   
                }else{//51以上   
                    if((size-25)%26==0){   
                        endSuffix = (char)('A'+25);   
                        endPrefix = (char)(endPrefix + (size-25)/26 - 1);   
                    }else{   
                        endSuffix = (char)('A'+(size-25)%26-1);   
                        endPrefix = (char)(endPrefix + (size-25)/26);   
                    }   
                }   
                return "$"+start+"$"+order+":$"+endPrefix+endSuffix+"$"+order;   
            }   
        }else{   
            if(size<=26){   
                char end = (char)(start+size-1);   
                return "$"+start+"$"+order+":$"+end+"$"+order;   
            }else{   
                char endPrefix = 'A';   
                char endSuffix = 'A';   
                if(size%26==0){   
                    endSuffix = (char)('A'+25);   
                    if(size>52&&size/26>0){   
                        endPrefix = (char)(endPrefix + size/26-2);   
                    }   
                }else{   
                    endSuffix = (char)('A'+size%26-1);   
                    if(size>52&&size/26>0){   
                        endPrefix = (char)(endPrefix + size/26-1);   
                    }   
                }   
                return "$"+start+"$"+order+":$"+endPrefix+endSuffix+"$"+order;   
            }   
        }   
    } 
    
    /**  
     * 使用已定义的数据源方式设置一个数据验证  
     * @param formulaString  
     * @param naturalRowIndex  
     * @param naturalColumnIndex  
     * @return  
     */ 
    public static DataValidation getDataValidationByFormula(String formulaString,int naturalRowIndex,int naturalColumnIndex){   
        //加载下拉列表内容     
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);    
        //设置数据有效性加载在哪个单元格上。     
        //四个参数分别是：起始行、终止行、起始列、终止列     
        int firstRow = naturalRowIndex-1;   
        int lastRow = naturalRowIndex-1;   
        int firstCol = naturalColumnIndex-1;   
        int lastCol = naturalColumnIndex-1;   
        CellRangeAddressList regions=new CellRangeAddressList(firstRow,lastRow,firstCol,lastCol);     
        //数据有效性对象    
        DataValidation data_validation_list = new HSSFDataValidation(regions,constraint);   
        //设置输入信息提示信息   
        data_validation_list.createPromptBox("下拉选择提示","请使用下拉方式选择合适的值！");   
        //设置输入错误提示信息   
        data_validation_list.createErrorBox("选择错误提示","你输入的值未在备选列表中，请下拉选择合适的值！");   
        return data_validation_list;   
    } 
    
}